﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FJERP.Model;
using System.Data.SqlClient;
using System.Data;
using System.Web.Script.Serialization;
using System.Collections;

namespace FJERP.DAL
{
   public  class MemberCardDAL
    {
        SQLHelper h = new SQLHelper();
        /// <summary>
        /// 分页显示会员列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <returns></returns>
        public List<MemberCard> ShowMemberCard(int PageSize, int PageIndex)
        {
            string sql = "select top " + PageSize + " * from(select ROW_NUMBER() over(order by MemberCard_Id)as nid ,MemberCard_Id,MemberCard_Name,MemberCard_owner,MemberCard_Cnum,MemberCard_CardID,MemberCard_ingeral,MemberCard_sphere,Rank_Name,MemberCard_State from MemberCard) as temp where nid> (" + PageIndex + " - 1) * " + PageSize + " ";
            List<MemberCard> list = new List<MemberCard>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                MemberCard m = new MemberCard();
                m.MemberCard_Id = Convert.ToInt32(item["MemberCard_Id"]);
                m.MemberCard_Name = item["MemberCard_Name"].ToString();
                m.MemberCard_owner = item["MemberCard_owner"].ToString();
                m.MemberCard_Cnum = item["MemberCard_Cnum"].ToString();
                m.MemberCard_CardID = item["MemberCard_CardID"].ToString();
                m.MemberCard_ingeral = Convert.ToInt32(item["MemberCard_ingeral"]);
                m.MemberCard_sphere = Convert.ToInt32(item["MemberCard_sphere"]);
                m.Rank_Name = item["Rank_Name"].ToString();
                m.MemberCard_State = Convert.ToInt32(item["MemberCard_State"]);
                list.Add(m);
            }
            return list;

        }
        /// <summary>
        /// 获取总记录数
        /// </summary>
        /// <returns></returns>
        public int Count()
        {
            string sql = "select COUNT(*) from MemberCard";
            return Convert.ToInt32(h.ExecScalar(sql));
        }
        /// <summary>
        /// 会员信息添加
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int MenBerCardAdd(MemberCard m)
        {
            string MemBer = "MemberCard_Add";
            SqlParameter[] par = new SqlParameter[] {
                new SqlParameter("@name",m.MemberCard_Name),
                new SqlParameter("@owner",m.MemberCard_owner),
                new SqlParameter("@Cnum",m.MemberCard_Cnum),
                new SqlParameter("@CardID",m.MemberCard_CardID),
                new SqlParameter("@ingeral",m.MemberCard_ingeral),
                new SqlParameter("@sphere",m.MemberCard_sphere),
                new SqlParameter("@Rname",m.Rank_Name),
                new SqlParameter("@State",m.MemberCard_State),
            };

            return h.ExecNonquery_Proc(MemBer, par);
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Del(int id)
        {
            string sql = "delete MemberCard where MemberCard_Id=" + id + "";
            return h.ExecNonquery(sql);
        }

        /// <summary>
        /// 根据id获取单条信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetMemberById(int id)
        {
            string sql = "select * from MemberCard where MemberCard_Id=" + id + "";
            DataTable table = h.ExecDataTable(sql);
            JavaScriptSerializer js = new JavaScriptSerializer();
            ArrayList list = new ArrayList();
            foreach (DataRow item in table.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn cl in table.Columns)
                {
                    dic.Add(cl.ColumnName, item[cl.ColumnName]);
                }
                list.Add(dic);
            }
            return js.Serialize(list);
        }
        /// <summary>
        /// 修改会员
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int Upd(MemberCard m)
        {
            string proc = "UpdMemberCard";
            SqlParameter[] par = new SqlParameter[]
            {
                new SqlParameter("@MemberCard_Id",m.MemberCard_Id),
                new SqlParameter("@MemberCard_Name",m.MemberCard_Name),
                new SqlParameter("@MemberCard_owner",m.MemberCard_owner),
                new SqlParameter("@MemberCard_Cnum",m.MemberCard_Cnum),
                new SqlParameter("@MemberCard_CardID",m.MemberCard_CardID),
                new SqlParameter("@MemberCard_ingeral",m.MemberCard_ingeral),
                new SqlParameter("@MemberCard_sphere",m.MemberCard_sphere),
                new SqlParameter("@Rank_Name",m.Rank_Name),
                new SqlParameter("@MemberCard_State",m.MemberCard_State)
            };
            return h.ExecNonquery_Proc(proc, par);
        }

        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="MemberCard_State"></param>
        /// <param name="MemberCard_Id"></param>
        /// <returns></returns>
        public int UptState(int MemberCard_State, int MemberCard_Id)
        {
            string str = "update dbo.MemberCard set MemberCard_State=" + MemberCard_State + " where MemberCard_Id=" + MemberCard_Id + "";
            return h.ExecNonquery(str);
        }

        /// <summary>
        /// 查询会员列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <returns></returns>
        public List<MemberCard> ShowMemberCard_Sel(int PageSize, int PageIndex, string MemberCard_owner, string MemberCard_Name)
        {
            string sql = "select top " + PageSize + " * from(select ROW_NUMBER() over(order by MemberCard_Id)as nid ,MemberCard_Id,MemberCard_Name,MemberCard_owner,MemberCard_Cnum,MemberCard_CardID,MemberCard_ingeral,MemberCard_sphere,Rank_Name,MemberCard_State from MemberCard)as temp where nid> (" + PageIndex + " - 1) * " + PageSize + "";
            if (!string.IsNullOrEmpty(MemberCard_owner))
            {
                sql += "and  MemberCard_owner like '%" + MemberCard_owner + "%'";
            }
            if (!string.IsNullOrEmpty(MemberCard_Name))
            {
                sql += "and MemberCard_Name like '%" + MemberCard_Name + "%'";
            }
            List<MemberCard> list = new List<MemberCard>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                MemberCard m = new MemberCard();
                m.MemberCard_Id = Convert.ToInt32(item["MemberCard_Id"]);
                m.MemberCard_Name = item["MemberCard_Name"].ToString();
                m.MemberCard_owner = item["MemberCard_owner"].ToString();
                m.MemberCard_Cnum = item["MemberCard_Cnum"].ToString();
                m.MemberCard_CardID = item["MemberCard_CardID"].ToString();
                m.MemberCard_ingeral = Convert.ToInt32(item["MemberCard_ingeral"]);
                m.MemberCard_sphere = Convert.ToInt32(item["MemberCard_sphere"]);
                m.Rank_Name = item["Rank_Name"].ToString();
                m.MemberCard_State = Convert.ToInt32(item["MemberCard_State"]);
                list.Add(m);
            }
            return list;
        }
    }
}
